#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys

from PyQt5.QtWidgets import (QTableView, QMessageBox, QHeaderView,
                             QDialog, QFileDialog, QApplication)
from PyQt5.QtCore import Qt, QSortFilterProxyModel
from PyQt5.QtSql import QSqlTableModel

from database.db_manager import DatabaseManager
from view.custom_dialog import AdvancedQueryDialog, RecordFormDialog, ColumnSelectionDialog



class DatabaseView:
    """数据库模块视图，负责数据表格的显示和操作"""
    
    def __init__(self, ui):
        self.ui = ui

        # 初始化数据库管理器
        self.db_manager = None
        
        # 当前查询条件
        self.current_query_conditions = []
        
        # 初始化UI
        self.init_ui()
        
        # 连接数据库
        self.connect_database()


    def init_ui(self):
        """初始化用户界面"""

        # 表选择下拉框
        self.ui.Select_DbTable_comboBox.addItems(["wells",])
        self.ui.Select_DbTable_comboBox.currentIndexChanged.connect(self.on_table_changed)
        self.ui.Select_DbTable_comboBox.setMinimumWidth(150)
        self.ui.Select_DbTable_comboBox.setToolTip("选择要查看的数据库表")


        # 快速搜索
        self.ui.Search_lineEdit.textChanged.connect(self.on_search_text_changed)
        self.ui.Search_lineEdit.setMinimumWidth(200)
        self.ui.Search_lineEdit.setToolTip("输入关键字在当前表中搜索")

        # 高级查询按钮
        self.ui.Advanced_Search_pushButton.clicked.connect(self.show_advanced_query)
        self.ui.Advanced_Search_pushButton.setToolTip("打开高级查询对话框")
        
        # 列选择按钮
        try:
            self.ui.Show_Col_pushButton.clicked.connect(self.show_column_selection_dialog)
            self.ui.Show_Col_pushButton.setToolTip("选择要显示的列")
        except AttributeError:
            # 如果按钮不存在，则忽略
            pass


        # 创建表格视图
        self.ui.DB_Show_tableView.setSelectionBehavior(QTableView.SelectRows)
        self.ui.DB_Show_tableView.setSelectionMode(QTableView.ExtendedSelection)
        self.ui.DB_Show_tableView.setSortingEnabled(True)
        self.ui.DB_Show_tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.ui.DB_Show_tableView.horizontalHeader().setStretchLastSection(True)
        self.ui.DB_Show_tableView.setAlternatingRowColors(True)  # 启用交替行颜色
        self.ui.DB_Show_tableView.setShowGrid(True)  # 显示网格
        self.ui.DB_Show_tableView.setGridStyle(Qt.SolidLine)
        self.ui.DB_Show_tableView.setEditTriggers(QTableView.NoEditTriggers)  # 禁止直接编辑
        self.ui.DB_Show_tableView.verticalHeader().setVisible(False)  # 隐藏垂直表头（行号）
        header = self.ui.DB_Show_tableView.horizontalHeader()
        # 修改列宽调整模式，允许用户手动调节列宽
        for i in range(header.count()):
            header.setSectionResizeMode(i, QHeaderView.Interactive)
        # 最后一列可以拉伸以填充剩余空间
        header.setStretchLastSection(True)
        # 注释掉这行代码，避免覆盖前面的列宽设置
        # header.setSectionResizeMode(QHeaderView.Stretch)

        # 添加记录按钮
        self.ui.Add_pushButton.clicked.connect(self.add_record)
        self.ui.Add_pushButton.setToolTip("在当前表中添加新记录")

        # 编辑记录按钮
        self.ui.Updata_pushButton.clicked.connect(self.edit_record)
        self.ui.Updata_pushButton.setToolTip("编辑选中的记录")

        # 删除记录按钮
        self.ui.Del_pushButton_2.clicked.connect(self.delete_record)
        self.ui.Del_pushButton_2.setToolTip("删除选中的记录")

        # 刷新按钮
        self.ui.F5_pushButton.clicked.connect(self.refresh_table)
        self.ui.F5_pushButton.setToolTip("刷新表格数据")

        # 初始化表格模型
        self.table_model = None
        self.search_proxy_model = QSortFilterProxyModel()
        self.search_proxy_model.setFilterCaseSensitivity(Qt.CaseInsensitive)

        # 初始状态下禁用按钮
        self.set_controls_enabled(False)
    
    def connect_database(self):
        """连接数据库"""
        if not self.db_manager:
            self.db_manager = DatabaseManager()
            
            # 连接信号
            self.db_manager.connection_established.connect(self.on_connection_established)
            self.db_manager.query_completed.connect(self.on_query_completed)
        
        # 尝试连接
        self.db_manager.connect()
    
    def on_connection_established(self, success, message):
        """数据库连接建立回调
        
        Args:
            success: 连接是否成功
            message: 连接消息
        """
        if success:
            self.set_controls_enabled(True)
            self.load_table_data()
        else:
            QMessageBox.warning(self.ui.DB_page_1, "连接错误", message)
            self.set_controls_enabled(False)
    
    def on_query_completed(self, success, message):
        """查询完成回调
        
        Args:
            success: 查询是否成功
            message: 查询消息
        """
        if not success:
            QMessageBox.warning(self.ui.DB_page_1, "查询错误", message)
    
    def set_controls_enabled(self, enabled):
        """设置控件启用状态
        
        Args:
            enabled: 是否启用
        """
        self.ui.Select_DbTable_comboBox.setEnabled(enabled)
        self.ui.Search_lineEdit.setEnabled(enabled)
        self.ui.Advanced_Search_pushButton.setEnabled(enabled)
        self.ui.Add_pushButton.setEnabled(enabled)
        self.ui.Updata_pushButton.setEnabled(enabled)
        self.ui.Del_pushButton_2.setEnabled(enabled)
        self.ui.F5_pushButton.setEnabled(enabled)
    
    def load_table_data(self):
        """加载表格数据"""
        if not self.db_manager or not self.db_manager.connected:
            return
            
        # 获取当前选择的表名
        table_name = self.ui.Select_DbTable_comboBox.currentText()
        
        # 创建表格模型
        self.table_model = QSqlTableModel(self.ui.DB_page_1, self.db_manager.qt_db)
        self.table_model.setTable(table_name)
        self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.table_model.select()
        # 设置搜索代理模型
        self.search_proxy_model.setSourceModel(self.table_model)
        
        # 将搜索代理模型设置到表格视图上
        self.ui.DB_Show_tableView.setModel(self.search_proxy_model)
        
        # 更新表头过滤器
        # self.filter_header.initialize()

    def on_table_changed(self):
        """表格选择变更处理"""
        self.load_table_data()
    
    def on_search_text_changed(self, text):
        """搜索文本变更处理
        
        Args:
            text: 搜索文本
        """
        self.search_proxy_model.setFilterFixedString(text)
        self.search_proxy_model.setFilterKeyColumn(-1)  # 搜索所有列

    
    def show_advanced_query(self):
        """显示高级查询对话框"""
        if not self.db_manager or not self.db_manager.connected:
            return
            
        # 获取当前表的列名
        column_names = []
        for i in range(self.table_model.columnCount()):
            column_names.append(self.table_model.headerData(i, Qt.Horizontal))
        
        # 创建高级查询对话框，传入当前已有的查询条件
        dialog = AdvancedQueryDialog(column_names, self.ui.DB_page_1, initial_conditions=self.current_query_conditions)
        if dialog.exec_() == QDialog.Accepted:
            # 获取查询条件
            conditions = dialog.get_query_conditions()
            self._apply_advanced_query(conditions)
    
    def _apply_advanced_query(self, conditions):
        """应用高级查询
        
        Args:
            conditions: 查询条件列表 [(逻辑关系, 列名, 操作符, 值), ...] 或 [(列名, 操作符, 值), ...]
        """
        if not conditions:
            # 清除当前条件
            self.current_query_conditions = []
            # 清除过滤器
            self.table_model.setFilter("")
            self.table_model.select()
            return
            
        # 保存当前查询条件，以便后续恢复
        self.current_query_conditions = conditions.copy()
            
        # 构建查询字符串
        where_clauses = []
        logic_operators = []
        
        for i, cond in enumerate(conditions):
            # 处理条件格式
            if len(cond) == 4:  # 新格式: (逻辑关系, 列名, 操作符, 值)
                logic, column, operator, value = cond
                if i > 0:  # 第一个条件不需要逻辑关系
                    logic_operators.append(logic)
            else:  # 旧格式: (列名, 操作符, 值)
                column, operator, value = cond
                if i > 0:  # 第一个条件不需要逻辑关系
                    logic_operators.append("AND")  # 默认AND关系
            
            # 获取列索引
            column_index = -1
            for j in range(self.table_model.columnCount()):
                if self.table_model.headerData(j, Qt.Horizontal) == column:
                    column_index = j
                    break
            
            if column_index >= 0:
                # 获取实际的列名
                real_column = self.table_model.record().fieldName(column_index)
                
                # 获取字段类型
                field = self.table_model.record().field(column_index)
                is_numeric = field.type() in [2, 3, 4, 6]  # 整数和浮点数类型
                
                # 处理不同的操作符，并根据字段类型正确处理值
                try:
                    if operator == "包含":
                        where_clauses.append(f"{real_column} LIKE '%{value}%'")
                    elif operator == "等于":
                        if is_numeric and value.strip():
                            where_clauses.append(f"{real_column} = {float(value)}")
                        else:
                            where_clauses.append(f"{real_column} = '{value}'")
                    elif operator == "大于":
                        if is_numeric and value.strip():
                            where_clauses.append(f"{real_column} > {float(value)}")
                        else:
                            where_clauses.append(f"{real_column} > '{value}'")
                    elif operator == "小于":
                        if is_numeric and value.strip():
                            where_clauses.append(f"{real_column} < {float(value)}")
                        else:
                            where_clauses.append(f"{real_column} < '{value}'")
                    elif operator == "大于等于":
                        if is_numeric and value.strip():
                            where_clauses.append(f"{real_column} >= {float(value)}")
                        else:
                            where_clauses.append(f"{real_column} >= '{value}'")
                    elif operator == "小于等于":
                        if is_numeric and value.strip():
                            where_clauses.append(f"{real_column} <= {float(value)}")
                        else:
                            where_clauses.append(f"{real_column} <= '{value}'")
                except ValueError:
                    # 如果数值转换失败，使用字符串比较
                    if operator == "等于":
                        where_clauses.append(f"{real_column} = '{value}'")
                    elif operator in ["大于", "小于", "大于等于", "小于等于"]:
                        # 对于非数值字段，这些比较可能没有意义，但仍然执行
                        op_map = {"大于": ">", "小于": "<", "大于等于": ">=", "小于等于": "<="}
                        where_clauses.append(f"{real_column} {op_map[operator]} '{value}'")
        
        # 组合条件，应用用户选择的逻辑关系
        if where_clauses:
            if len(where_clauses) == 1:
                filter_str = where_clauses[0]
            else:
                filter_str = where_clauses[0]
                for i, clause in enumerate(where_clauses[1:], 1):
                    if i <= len(logic_operators):
                        operator = logic_operators[i-1]
                        filter_str += f" {operator} {clause}"
            
            # 设置过滤器
            self.table_model.setFilter(filter_str)
            self.table_model.select()
            
            # 检查是否有结果
            if self.table_model.rowCount() == 0:
                QMessageBox.information(self.ui.DB_page_1, "查询结果", "没有找到匹配的记录")
        else:
            # 清除当前条件
            self.current_query_conditions = []
            # 清除过滤器
            self.table_model.setFilter("")
            self.table_model.select()
    
    def add_record(self):
        """添加记录"""
        if not self.db_manager or not self.db_manager.connected:
            return
            
        # 获取当前表名
        table_name = self.ui.Select_DbTable_comboBox.currentText()
        
        # 创建数据输入表单对话框
        dialog = RecordFormDialog(self.table_model, parent=self.ui.DB_page_1)
        
        # 如果用户点击确定
        if dialog.exec_() == QDialog.Accepted:
            # 获取表单数据
            record = dialog.get_record()
            
            # 验证表单数据
            if record is None:
                QMessageBox.warning(self.ui.DB_page_1, "验证失败", "请填写所有必填字段")
                return
                
            # 在表格末尾添加一行
            row = self.table_model.rowCount()
            self.table_model.insertRow(row)
            
            # 设置记录值
            for i in range(record.count()):
                field_name = record.fieldName(i)
                if not record.isNull(i) and field_name != self.table_model.primaryKey().fieldName(0):
                    self.table_model.setData(
                        self.table_model.index(row, self.table_model.record().indexOf(field_name)),
                        record.value(i)
                    )
            
            # 提交更改
            if self.table_model.submitAll():
                QMessageBox.information(self.ui.DB_page_1, "成功", "记录添加成功")
            else:
                QMessageBox.warning(self.ui.DB_page_1, "错误", f"添加记录失败: {self.table_model.lastError().text()}")
                self.table_model.revertAll()
        else:
            # 用户取消添加
            pass
    
    def edit_record(self):
        """编辑记录"""
        # 获取选中的行
        selected_rows = self.ui.DB_Show_tableView.selectionModel().selectedRows()
        if not selected_rows:
            QMessageBox.information(self.ui.DB_page_1, "提示", "请先选择要编辑的记录")
            return
            
        # 检查是否选择了多条记录
        if len(selected_rows) > 1:
            QMessageBox.information(self.ui.DB_page_1, "提示", "当前不支持批量修改，请每次只选择一条记录")
            return

        # 从列表中获取第一个选中的行索引
        row = selected_rows[0].row()
        
        # 获取当前记录
        record = self.table_model.record(row)
        
        # 创建编辑对话框
        dialog = RecordFormDialog(self.table_model, record, self.ui.DB_page_1)
        
        # 如果用户确认修改
        if dialog.exec_() == QDialog.Accepted:
            # 获取修改后的记录
            updated_record = dialog.get_record()
            
            # 验证失败
            if updated_record is None:
                QMessageBox.warning(self.ui.DB_page_1, "错误", "请填写所有必填字段")
                return
                
            # 更新记录
            for i in range(updated_record.count()):
                field_name = updated_record.fieldName(i)
                if field_name != self.table_model.primaryKey().fieldName(0):  # 跳过主键
                    self.table_model.setData(
                        self.table_model.index(row, i),
                        updated_record.value(i)
                    )
            
            # 提交更改
            if self.table_model.submitAll():
                QMessageBox.information(self.ui.DB_page_1, "成功", "记录更新成功")
            else:
                QMessageBox.warning(self.ui.DB_page_1, "错误", f"更新记录失败: {self.table_model.lastError().text()}")
                self.table_model.revertAll()
    
    def delete_record(self):
        """删除记录"""
        # 获取选中的行
        selected_rows = self.ui.DB_Show_tableView.selectionModel().selectedRows()
        if not selected_rows:
            QMessageBox.information(self.ui.DB_page_1, "提示", "请先选择要删除的记录")
            return
            
        # 确认删除
        reply = QMessageBox.question(self.ui.DB_page_1, "确认删除",
                                    f"确定要删除选中的 {len(selected_rows)} 条记录吗？",
                                    QMessageBox.Yes | QMessageBox.No, 
                                    QMessageBox.No)
        
        if reply != QMessageBox.Yes:
            return
            
        # 从后向前删除行（避免索引变化）
        rows = []
        for index in selected_rows:
            rows.append(index.row())
        rows.sort(reverse=True)
        for row in rows:
            self.table_model.removeRow(row)
            
        # 提交更改
        if self.table_model.submitAll():
            QMessageBox.information(self.ui.DB_page_1, "成功", "记录删除成功")
        else:
            QMessageBox.warning(self.ui.DB_page_1, "错误", f"删除记录失败: {self.table_model.lastError().text()}")
            self.table_model.revertAll()
    
    def refresh_table(self):
        """刷新表格数据，保留当前的搜索和过滤条件"""
        if not self.table_model:
            return
            
        # 保存当前状态
        current_search_text = self.ui.Search_lineEdit.text()
        current_query_conditions = self.current_query_conditions.copy() if hasattr(self, 'current_query_conditions') else []
        
        # 重新加载数据
        self.load_table_data()
        
        # 恢复搜索条件
        if current_search_text:
            self.ui.Search_lineEdit.setText(current_search_text)
            self.search_proxy_model.setFilterFixedString(current_search_text)
            self.search_proxy_model.setFilterKeyColumn(-1)
        
        # 恢复高级查询条件
        if current_query_conditions:
            self.current_query_conditions = current_query_conditions
            self._apply_advanced_query(current_query_conditions)

    
    def import_data(self):
        """导入数据"""
        # 选择导入文件
        file_path, _ = QFileDialog.getOpenFileName(self, "选择导入文件", "", 
                                                 "CSV文件 (*.csv);;Excel文件 (*.xlsx *.xls);;所有文件 (*)")
        
        if not file_path:
            return
            
        # TODO: 实现数据导入功能
        QMessageBox.information(None, "提示", f"数据导入功能尚未实现\n选择的文件: {file_path}")
    
    def export_data(self):
        """导出数据"""
        # 选择导出文件
        file_path, _ = QFileDialog.getSaveFileName(self, "导出数据", "", 
                                                "CSV文件 (*.csv);;Excel文件 (*.xlsx);;所有文件 (*)")
        
        if not file_path:
            return
            
        # TODO: 实现数据导出功能
        QMessageBox.information(None, "提示", f"数据导出功能尚未实现\n选择的文件: {file_path}")

    def show_column_selection_dialog(self):
        """显示列选择对话框，允许用户控制表格中显示的列"""
        if not self.table_model:
            return
        
        # 获取当前表格所有列的名称
        column_names = []
        from PyQt5.QtCore import QModelIndex
        for i in range(self.table_model.columnCount(QModelIndex())):
            header = self.table_model.headerData(i, Qt.Horizontal)
            if header is not None:
                column_names.append(str(header))
        
        # 获取当前显示的列索引
        visible_columns = []
        model = self.ui.DB_Show_tableView.model()
        if model:
            for i in range(model.columnCount(QModelIndex())):
                if not self.ui.DB_Show_tableView.isColumnHidden(i):
                    visible_columns.append(i)
        
        # 创建列选择对话框
        dialog = ColumnSelectionDialog(column_names, visible_columns, parent=self.ui.DB_page_1)
        
        if dialog.exec_() == QDialog.Accepted:
            # 获取用户选择的列索引
            selected_columns = dialog.get_selected_columns()
            
            # 设置列的显示状态
            model = self.ui.DB_Show_tableView.model()
            if model:
                for i in range(model.columnCount(QModelIndex())):
                    # 隐藏未选中的列
                    self.ui.DB_Show_tableView.setColumnHidden(i, i not in selected_columns)
                
                # 重新设置表格列宽，确保可见列能够适当显示
                header = self.ui.DB_Show_tableView.horizontalHeader()
                for i in range(model.columnCount(QModelIndex())):
                    if not self.ui.DB_Show_tableView.isColumnHidden(i):
                        header.setSectionResizeMode(i, QHeaderView.Interactive)
                
                # 让最后一个可见列拉伸以填充剩余空间
                last_visible_col = -1
                for i in range(model.columnCount(QModelIndex()) - 1, -1, -1):
                    if not self.ui.DB_Show_tableView.isColumnHidden(i):
                        last_visible_col = i
                        break
                
                if last_visible_col >= 0:
                    header.setSectionResizeMode(last_visible_col, QHeaderView.Stretch)

